Customer Case Study · HR · People Analytics & MIS
The power transmission company's HR team spent two full days every month turning an employee master into a people MIS. HMC replaced the copy-paste with a VBA macro: pick the month, press Run, and the pivots, charts and checks are ready in under two hours, with no manual errors.
Figures indicative; validated metrics available on request.
Every month HR exported the employee master, cleaned it, rebuilt a dozen pivots, recounted headcount, diversity, tenure and age, and redrew the charts for leadership. The steps never changed; only the month did.
Building the monthly HR report by hand took two full days, so insight reached leaders late.
Manual counts, filters and copy-paste put the integrity of vital HR data at risk every cycle.
The laborious routine drained productivity and crowded out strategic HR initiatives.
Geography, grade, gender, tenure, age and talent landscape all had to agree, and checking that by eye was slow.
Built inside the HR team's own workbook, so nothing new had to be installed or learned.
Select a step to see what happened.
We sat with the HR analyst through a full monthly build.
Deliverable: Step map with time and error hotspots.
The export lands in an MIS sheet; everything else is generated.
Deliverable: Dashboard layout and band definitions.
A single macro rebuilds the report from scratch.
Deliverable: Kamal Solution VBA.xlsm with modules and a RunMacro sheet.
The first live month ran side by side with the manual report.
Deliverable: Parallel-run sign-off and guide.
New views became additions to the macro, not extra hours.
Deliverable: Modular code: Mod_Final, Mod_FinalCharts, Mod_FinalSnippets.
First the workbook as built, then a working replica of the macro on synthetic data, then a race between the old and new way of building the report.
Click any screen to enlarge it. Employee personal details in the MIS sheet are pixelated.
A replica of the workbook on a synthetic workforce shaped like the Aug-22 report. Choose a month and press Run: the steps execute, the dashboard builds, and the checks confirm every total reconciles.
Press Run FinalHM. The macro deletes the old PivotTable sheet and rebuilds everything from the MIS data.
Synthetic workforce for illustration: totals for Aug-22 follow the original dashboard; individuals, functions and other months are generated.
Both lanes build the same monthly MIS. Press Race: the manual lane works through a two-day routine and picks up errors on the way; the macro finishes in two hours with every check green.
Report day used to swallow the start of every month. Now it is a morning task, and the numbers no longer need a second pair of eyes.
Working days, coloured by time spent on the MIS
Errors found in the monthly MIS, by section
Figures indicative; validated metrics available on request.
A routine that is the same every month is a macro waiting to be written.
Deleting and recreating the pivots each run means no stale filters or leftovers from last month.
When totals reconcile automatically, review time goes into reading the numbers, not checking them.